package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import POJO.Terminal;
import POJO.User;


@Repository
public class UserDao {
	
	@Autowired
	JdbcTemplate jdbcTemplate;

	public User RFIDValid(String RFID) {
		final User user=new User();
		String getUserInfoSQL = "SELECT user_mobileNum,user_rfid FROM user WHERE user_rfid=? and user_status!=0";
		Object[] params=new Object[]{RFID};
		jdbcTemplate.query(getUserInfoSQL,params, new RowCallbackHandler(){
			public void processRow(ResultSet rs) throws SQLException {
				user.setMobileNum(rs.getString("user_mobileNum"));
				user.setRFID(rs.getString("user_rfid"));
			}
		});
		if(user.getRFID()!=null){
			return user;
		}else{
			return null;
		}
	}
	
	public boolean isUserExisted(String phoneNum){
			String isUserExistedSQL="SELECT count(user_id) from user where user_mobileNum=?;";
			Object[] params=new Object[]{phoneNum};
			int result=jdbcTemplate.queryForInt(isUserExistedSQL, params);
			if(result==0){
				return false;
			}else{
				return true;
			}
	}
	
	/**
	 * 检测用户是否注册
	 * @param phoneNum
	 * @return
	 */
	public boolean isUserRegister(String phoneNum){
		String isisUserRegisterSql="SELECT user_status FROM user WHERE user_mobileNum=?";
		Object[] params=new Object[]{phoneNum};
		int result=0;
		try {
			 result=jdbcTemplate.queryForInt(isisUserRegisterSql, params);
		} catch (Exception e) {
			return false;
		}
		if(result==2){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 检测RFID是否已经注册
	 * @param RFID
	 * @return
	 */
	public boolean isRFIDRegister(String RFID){
		String isisRFIDRegisterSql="SELECT count(*) FROM user WHERE user_rfid=?";
		Object[] params=new Object[]{RFID};
		int result=-1;
		 result=jdbcTemplate.queryForInt(isisRFIDRegisterSql, params);
		 if(result==1){
			 return true;
		 }else{
			 return false;
		 }
	}
	
	
	/**
	 * 增加一条用户记录
	 * @param user
	 * @param terminal
	 * @return
	 */
	public int saveUserInfo(final User user,final Terminal terminal){
		final String saveUserInfoSQL="INSERT INTO `user` (`user_mobileNum`, `user_rfid`,`user_status`,`user_addr`) VALUES (?,?,?,?)";
//		Object[] params=new Object[]{user.getMobileNum(),user.getRFID(),1,terminal.getTerminalID()};
//		int result=jdbcTemplate.update(saveUserInfoSQL,params);
//		if(result==0){
//			return false;
//		}else{
//			return true;
//		}
		
		
		KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection conn)
                    throws SQLException {
                PreparedStatement ps = conn.prepareStatement(saveUserInfoSQL,Statement.RETURN_GENERATED_KEYS);
                ps.setString(1,user.getMobileNum());  
                ps.setString(2,user.getRFID());  
                ps.setInt(3,1);  
                ps.setInt(4,terminal.getTerminalID());  
                return ps;
            }
        },keyHolder);
        System.out.println("index number: "+keyHolder.getKey().intValue());
        return keyHolder.getKey().intValue();
	}
	
	/**
	 * 给定用户的手机号，判定RFID字段上是否为空
	 * @param phoneNum
	 * @return
	 */
	public boolean isRFIDNull(String phoneNum){
		String isRFIDNull="SELECT user_rfid FROM user where user_mobileNum=? and user_status!=0;";
		Object[] params=new Object[]{phoneNum};
		try {
			String result=(String)jdbcTemplate.queryForObject(isRFIDNull, params, String.class);
			if(result!=null){
				return false;
			}else{
				return true;
			}
		} catch (Exception e) {
			//查询不到手机号码
			return true;
		}

	}
	/**
	 * 在绑定业务流程中，如果手机号已经存在，直接更新RFID,user_status和user_addr
	 * @param phoneNum
	 * @param RFID
	 * @return
	 */
	public boolean updateRFID(String phoneNum, String RFID,Terminal terminal) {
		
		String updateRFIDSql="UPDATE `user` SET `user_rfid`=?,`user_addr`=?,`user_status`=1 WHERE `user_mobileNum`=?";
		Object[] params=new Object[]{RFID,terminal.getTerminalID(),phoneNum};
		int result=jdbcTemplate.update(updateRFIDSql,params);
		if(result==1){
			return true;
		}else{
			return false;
		}
		
	}
	
	/**
	 * 该操作必须配合isUserExisted一起使用！
	 * 如果user_status=0，表示该用户已经被删除
	 * @param phoneNum
	 * @return	true-仅仅用户处于删除状态
	 *  		false-用户不存在 or 处于激活状态
	 */
	public boolean isUserDelete(String phoneNum){
		String isUserDeleteSql="SELECT user_status FROM user where user_mobileNum=?;";
		Object[] params=new Object[]{phoneNum};		
		try {
			int result=jdbcTemplate.queryForInt(isUserDeleteSql,params);
			if(result==0){
				return true;
			}else{
				return false;
			}
		} catch (Exception e) {
			return false;
		}
	}

	
}
